﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class AnnexDAL
    {
        private const string Waming = "SqlServerDAL.AnnexDAL";

        public int Save(AnnexDto annexDto)
        {
            try
            {
                int returnValue;
                string sql;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@AnnexName", SqlDbType.VarChar) {Value = annexDto.AnnexName},
                                         new SqlParameter("@AnnexOriginalPath", SqlDbType.VarChar)
                                             {Value = annexDto.AnnexOriginalPath},
                                         new SqlParameter("@AnnexNarrowPath", SqlDbType.VarChar)
                                             {Value = annexDto.AnnexNarrowPath},
                                         new SqlParameter("@AnnexCreateDate", SqlDbType.DateTime)
                                             {Value = annexDto.AnnexDate},
                                         new SqlParameter("@AnnexSize", SqlDbType.Int) {Value = annexDto.AnnexSize},
                                         new SqlParameter("@AnnexHeight", SqlDbType.Int) {Value = annexDto.AnnexHeight},
                                         new SqlParameter("@AnnexWidth", SqlDbType.Int) {Value = annexDto.AnnexWidth},
                                         new SqlParameter("@AnnexDomain", SqlDbType.VarChar){Value = annexDto.AnnexDomain}
                                     };

                if (annexDto.AnnexID == 0)
                {
                    sql =
                        @"insert into Annex(AnnexName,AnnexOriginalPath,AnnexNarrowPath,AnnexCreateDate,AnnexSize,
                        AnnexHeight,AnnexWidth,AnnexDomain) values(@AnnexName,@AnnexOriginalPath,@AnnexNarrowPath,
                        @AnnexCreateDate,@AnnexSize,@AnnexHeight,@AnnexWidth,@AnnexDomain) select @@identity as resultId";
                    returnValue = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()));
                }
                else
                {
                    sql =
                        @"update Annex set AnnexName=@AnnexName,AnnexOriginalPath=@AnnexOriginalPath,AnnexNarrowPath=@AnnexNarrowPath,
                        AnnexCreateDate=@AnnexCreateDate,AnnexSize=@AnnexSize,AnnexHeight=@AnnexHeight,AnnexWidth=@AnnexWidth, 
                        AnnexDomain=@AnnexDomain where AnnexID=@AnnexID";
                    parameters.Add(new SqlParameter("@AnnexID", SqlDbType.Int) { Value = annexDto.AnnexID });
                    returnValue = SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                }

                return returnValue;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(AnnexDto annexDto)", ex);
                return -1;
            }
        }

        public Hashtable FindById(int id)
        {
            try
            {
                Hashtable ht = null;
                const string sql = "select * from Annex where AnnexID=@AnnexID";
                var param = new SqlParameter("@AnnexID", SqlDbType.Int) { Value = id };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, param))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindById(int id)", ex);
                return null;
            }
        }

        public bool Delete(int id)
        {
            try
            {
                const string sql = "delete from Annex where AnnexID=@AnnexID";
                var param = new SqlParameter("@AnnexID", SqlDbType.Int) { Value = id };
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, param);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Delete(int id)", ex);
                return false;
            }
        }

        public int SelectSizeSum(int userId)
        {
            try
            {
                const string sql = "select sum(a.AnnexSize) from Annex a inner join UserAlbum u on a.AnnexID=u.AlbumAnnexID";
                var parameters = new List<SqlParameter>();
                var whereSql = "";
                if (userId != 0)
                {
                    whereSql += " where u.AlbumUserID=@AlbumUserID";
                    parameters.Add(new SqlParameter("@AlbumUserID", SqlDbType.Int) { Value = userId });
                }
                var red = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql + whereSql, parameters.ToArray());
                return Convert.ToInt32(red);
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectSizeSum(int userId)", ex);
                return -1;
            }
        }
    }
}
